"""
Staff API routes - 员工管理
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from db.connection import execute_query

bp = Blueprint('staff', __name__, url_prefix='/api/staff')

@bp.route('', methods=['GET'])
def get_staff():
    """
    Get all staff members
    GET /api/staff
    """
    try:
        query = """
            SELECT staff_id, username, full_name, role, is_active
            FROM staff
            WHERE is_active = true
            ORDER BY staff_id ASC
        """
        
        staff = execute_query(query)
        
        return jsonify({
            'code': 200,
            'data': staff,
            'count': len(staff)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/<int:staff_id>', methods=['GET'])
def get_staff_member(staff_id):
    """
    Get a single staff member by ID
    GET /api/staff/:id
    """
    try:
        query = """
            SELECT staff_id, username, full_name, role, is_active
            FROM staff
            WHERE staff_id = %s
        """
        
        staff_member = execute_query(query, (staff_id,), fetch_one=True)
        
        if not staff_member:
            return jsonify({
                'code': 404,
                'message': 'Staff member not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'data': staff_member
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('', methods=['POST'])
def create_staff():
    """
    Create a new staff member
    POST /api/staff
    """
    try:
        data = request.json
        
        required_fields = ['username', 'password_hash', 'full_name', 'role']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'code': 400,
                    'message': f'Missing required field: {field}'
                }), 400
        
        query = """
            INSERT INTO staff (username, password_hash, full_name, role)
            VALUES (%s, %s, %s, %s)
            RETURNING staff_id, username, full_name
        """
        
        params = (
            data['username'],
            data['password_hash'],
            data['full_name'],
            data['role']
        )
        
        result = execute_query(query, params, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'Staff member created successfully',
            'data': result
        }), 201
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/<int:staff_id>', methods=['PUT'])
def update_staff(staff_id):
    """
    Update a staff member
    PUT /api/staff/:id
    """
    try:
        data = request.json
        
        update_fields = []
        params = []
        
        allowed_fields = ['full_name', 'role', 'is_active', 'password_hash']
        
        for field in allowed_fields:
            if field in data:
                update_fields.append(f"{field} = %s")
                params.append(data[field])
        
        if not update_fields:
            return jsonify({
                'code': 400,
                'message': 'No fields to update'
            }), 400
        
        params.append(staff_id)
        
        query = f"""
            UPDATE staff
            SET {', '.join(update_fields)}
            WHERE staff_id = %s
            RETURNING staff_id, username, full_name
        """
        
        result = execute_query(query, tuple(params), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
                'message': 'Staff member not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Staff member updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

@bp.route('/by-role', methods=['GET'])
def get_staff_by_role():
    """
    Get staff grouped by role
    GET /api/staff/by-role
    """
    try:
        query = """
            SELECT role, COUNT(*) as staff_count,
                   array_agg(full_name ORDER BY full_name) as staff_names
            FROM staff
            WHERE is_active = true
            GROUP BY role
            ORDER BY role
        """
        
        roles = execute_query(query)
        
        return jsonify({
            'code': 200,
            'data': roles
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
                'message': str(e)
            }), 500

